-- This script was generated by the Schema Diff utility in pgAdmin 4
-- For the circular dependencies, the order in which Schema Diff writes the objects is not very sophisticated
-- and may require manual changes to the script to ensure changes are applied in the correct order.
-- Please report an issue for any failure with the reproduction steps.

DROP POLICY IF EXISTS "User RBAC with user_api_keys table" ON public.request;

DROP POLICY IF EXISTS "User RBAC with user_api_keys table and request table" ON public.response;

CREATE OR REPLACE VIEW public.response_rbac
 AS
 SELECT res.id,
    res.created_at,
    res.body,
    res.request
   FROM response res
     JOIN request r ON r.id = res.request
     JOIN user_api_keys u ON r.auth_hash = u.api_key_hash
  WHERE auth.uid() = u.user_id;

ALTER TABLE public.response_rbac
    OWNER TO postgres;

GRANT ALL ON TABLE public.response_rbac TO authenticated;
GRANT ALL ON TABLE public.response_rbac TO postgres;
GRANT ALL ON TABLE public.response_rbac TO anon;
GRANT ALL ON TABLE public.response_rbac TO service_role;

CREATE OR REPLACE VIEW public.request_rbac
 AS
 SELECT r.id,
    r.created_at,
    r.body,
    r.path,
    r.auth_hash,
    r.user_id
   FROM request r
     JOIN user_api_keys u ON r.auth_hash = u.api_key_hash
  WHERE auth.uid() = u.user_id;

ALTER TABLE public.request_rbac
    OWNER TO postgres;

GRANT ALL ON TABLE public.request_rbac TO authenticated;
GRANT ALL ON TABLE public.request_rbac TO postgres;
GRANT ALL ON TABLE public.request_rbac TO anon;
GRANT ALL ON TABLE public.request_rbac TO service_role;

CREATE OR REPLACE VIEW public.user_metrics_rbac
 AS
 SELECT request.user_id,
    min(request.created_at) AS first_active,
    max(request.created_at) AS last_active,
    count(request.id) AS total_requests,
    count(request.id)::double precision / count(DISTINCT date_trunc('day'::text, request.created_at))::double precision AS average_requests_per_day_active,
    avg((((response.body ->> 'usage'::text)::json) ->> 'total_tokens'::text)::integer) AS average_tokens_per_request
   FROM request_rbac request
     LEFT JOIN response_rbac response ON response.request = request.id
  GROUP BY request.user_id;

ALTER TABLE public.user_metrics_rbac
    OWNER TO postgres;

GRANT ALL ON TABLE public.user_metrics_rbac TO authenticated;
GRANT ALL ON TABLE public.user_metrics_rbac TO postgres;
GRANT ALL ON TABLE public.user_metrics_rbac TO anon;
GRANT ALL ON TABLE public.user_metrics_rbac TO service_role;

CREATE OR REPLACE VIEW public.metrics_rbac
 AS
 SELECT ( SELECT avg(EXTRACT(epoch FROM response.created_at - request.created_at)) AS avg
           FROM request_rbac request
             LEFT JOIN response_rbac response ON response.request = request.id) AS average_response_time,
    ( SELECT avg((((response.body ->> 'usage'::text)::json) ->> 'total_tokens'::text)::integer) AS avg
           FROM response_rbac response) AS average_tokens_per_response;

ALTER TABLE public.metrics_rbac
    OWNER TO postgres;

GRANT ALL ON TABLE public.metrics_rbac TO authenticated;
GRANT ALL ON TABLE public.metrics_rbac TO postgres;
GRANT ALL ON TABLE public.metrics_rbac TO anon;
GRANT ALL ON TABLE public.metrics_rbac TO service_role;

CREATE OR REPLACE VIEW public.response_and_request_rbac
 AS
 SELECT response.body AS response_body,
    response.id AS response_id,
    response.created_at AS response_created_at,
    request.id AS request_id,
    request.body AS request_body,
    request.path AS request_path,
    request.created_at AS request_created_at,
    request.user_id AS request_user_id,
    user_api_keys.api_key_preview,
    user_api_keys.user_id
   FROM response
     LEFT JOIN request ON request.id = response.request
     LEFT JOIN user_api_keys ON user_api_keys.api_key_hash = request.auth_hash
  WHERE auth.uid() = user_api_keys.user_id;

ALTER TABLE public.response_and_request_rbac
    OWNER TO postgres;

GRANT ALL ON TABLE public.response_and_request_rbac TO authenticated;
GRANT ALL ON TABLE public.response_and_request_rbac TO postgres;
GRANT ALL ON TABLE public.response_and_request_rbac TO anon;
GRANT ALL ON TABLE public.response_and_request_rbac TO service_role;

DROP VIEW IF EXISTS public.response_and_request;

DROP VIEW IF EXISTS public.metrics;

DROP VIEW IF EXISTS public.user_metrics;
